﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Windows;
using System.Windows.Media;
using MySql.Data.MySqlClient;
using System.Collections.Concurrent;
using SqlSugar;
using static Org.BouncyCastle.Math.EC.ECCurve;
using System.Configuration;
using CosAging.Model;
using System.Data.SQLite;
using MySqlX.XDevAPI.Common;
using System.Collections;
using System.Windows.Forms;
using MessageBox = System.Windows.MessageBox;

namespace CosAging
{
    public class SQL
    {
        static readonly string strConn = "server=CNWQ01DB11\\SQLHOTEL;database=CNCC_Maintenance;uid=CNCC_Maintenance_RO ;pwd=B50T3@8H8i)$EHOmly5pGV2";
        static readonly long totalRow = 1;
        static readonly int getRow = 1000;
        static object OB = new object();
        static void MainT(string[] args)
        {
            //InsertOne();
            //InsertTwo();
            //InsertThree();
            //InsertFour();
            //Console.WriteLine("插入数据结束");
            //Console.ReadLine();
        }
        #region 方式一
        public static void InsertOne()
        {
            //Console.WriteLine("采用一条一条插入的方式实现");
            //Stopwatch sw = new Stopwatch();

            //using (SqlConnection conn = new SqlConnection(StrConnMsg)) //using中会自动Open和Close 连接。
            //{
            //    string sql = "INSERT INTO Product(Id,Name,Price) VALUES(newid(),@p,@d)";
            //    conn.Open();
            //    for (int i = 0; i < totalRow; i++)
            //    {
            //        using (SqlCommand cmd = new SqlCommand(sql, conn))
            //        {
            //            cmd.Parameters.AddWithValue("@p", "商品" + i);
            //            cmd.Parameters.AddWithValue("@d", i);
            //            sw.Start();
            //            cmd.ExecuteNonQuery();
            //            Console.WriteLine(string.Format("插入一条记录，已耗时{0}毫秒", sw.ElapsedMilliseconds));
            //        }
            //        if (i == getRow)
            //        {
            //            sw.Stop();
            //            break;
            //        }
            //    }
            //}
            //Console.WriteLine(string.Format("插入{0}条记录，每{4}条的插入时间是{1}毫秒,预估总得插入时间是{2}毫秒，{3}分钟", totalRow, sw.ElapsedMilliseconds, ((sw.ElapsedMilliseconds / getRow) * totalRow), GetMinute((sw.ElapsedMilliseconds / getRow * totalRow)), getRow));
        }
        public static int GetMinute(long l)
        {
            return (Int32)l / 60000;
        }
        #endregion
        #region 方式二
        public static DataTable GetTableSchema()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            {
                new DataColumn("ID",typeof(int)),
                new DataColumn("ServerTime",typeof(DateTime)),
                new DataColumn("DeviceName",typeof(string)),
                new DataColumn("状态",typeof(int)),

                new DataColumn("A相电压",typeof(float)),
                new DataColumn("B相电压",typeof(float)),
                new DataColumn("C相电压",typeof(float)),
                new DataColumn("AB线电压",typeof(float)),
                new DataColumn("BC线电压",typeof(float)),
                new DataColumn("CA线电压",typeof(float)),
                new DataColumn("A相电流",typeof(float)),
                new DataColumn("B相电流",typeof(float)),
                new DataColumn("C相电流",typeof(float)),
                new DataColumn("A相有功功率",typeof(float)),
                new DataColumn("B相有功功率",typeof(float)),
                new DataColumn("C相有功功率",typeof(float)),
                new DataColumn("总有功功率",typeof(float)),
                new DataColumn("A相无功功率",typeof(float)),
                new DataColumn("B相无功功率",typeof(float)),
                new DataColumn("C相无功功率",typeof(float)),
                new DataColumn("总无功功率",typeof(float)),
                new DataColumn("总视在功率",typeof(float)),
                new DataColumn("总功率因数",typeof(float)),
                new DataColumn("电网频率",typeof(float)),
                new DataColumn("正向有功电能",typeof(float))
            });



            return dt;
        }

    #endregion
        #region 方式三
    public static void InsertThree()
        {
            //Console.WriteLine("使用TVPs插入的实现方式");
            //Stopwatch sw = new Stopwatch();
            //DataTable dt = GetTableSchema();

            //using (SqlConnection conn = new SqlConnection(StrConnMsg))
            //{
            //    string sql = "INSERT INTO Product(Id,Name,Price) select Id,Name,Price from @TempTb";
            //    using (SqlCommand cmd = new SqlCommand(sql, conn))
            //    {
            //        SqlParameter catParam = cmd.Parameters.AddWithValue("@TempTb", dt);
            //        catParam.SqlDbType = SqlDbType.Structured;
            //        catParam.TypeName = "dbo.ProductTemp";
            //        conn.Open();
            //        sw.Start();

            //        for (int i = 0; i < totalRow; i++)
            //        {
            //            DataRow dr = dt.NewRow();
            //            dr[0] = Guid.NewGuid();
            //            dr[1] = string.Format("商品", i);
            //            dr[2] = (decimal)i;
            //            dt.Rows.Add(dr);
            //        }

            //        if (dt != null && dt.Rows.Count != 0)
            //        {
            //            cmd.ExecuteNonQuery();
            //            sw.Stop();
            //        }
            //        Console.WriteLine(string.Format("TVPs插入{0}条记录共花费{1}毫秒，{2}分钟", totalRow, sw.ElapsedMilliseconds, GetMinute(sw.ElapsedMilliseconds)));
            //    }
            //}
        }
        #endregion
        #region 方式四
        public static void InsertFour()
        {
            //Console.WriteLine("采用拼接批量SQL插入的方式实现");
            //Stopwatch sw = new Stopwatch();

            //using (SqlConnection conn = new SqlConnection(StrConnMsg)) //using中会自动Open和Close 连接。
            //{
            //    conn.Open();
            //    sw.Start();
            //    for (int j = 0; j < totalRow / getRow; j++)
            //    {
            //        StringBuilder sb = new StringBuilder();
            //        sb.Append("INSERT INTO Product(Id,Name,Price) VALUES");
            //        using (SqlCommand cmd = new SqlCommand())
            //        {

            //            for (int i = 0; i < getRow; i++)
            //            {
            //                sb.AppendFormat("(newid(),'商品{0}',{0}),", j * i + i);
            //            }
            //            cmd.Connection = conn;
            //            cmd.CommandText = sb.ToString().TrimEnd(',');
            //            cmd.ExecuteNonQuery();
            //        }
            //    }
            //    sw.Stop();
            //    Console.WriteLine(string.Format("插入{0}条记录，共耗时{1}毫秒，{1}分钟", totalRow, sw.ElapsedMilliseconds, GetMinute(sw.ElapsedMilliseconds)));
            //}
        }
        #endregion

        //ORM测试
        private static ConnectionConfig _config => new ConnectionConfig()
        {
            DbType = SqlSugar.DbType.MySql,
            ConnectionString = ConfigurationManager.ConnectionStrings["CMCCDB"].ConnectionString,
            IsAutoCloseConnection= true,//自动释放数据事务，如果存在事务，在事务结束后释放
            InitKeyType=InitKeyType.Attribute,//从实体特性中读取主键自增列信息        
        };
        public static void MySqlUpdateTest()
        {
            try
            {
                using (SqlSugarClient client = new SqlSugarClient(_config))
                {
                    //var data1 = new agingrecord() { 测试总结果="失败",作业员="测试" };
                    //client.Insertable(data1).ExecuteCommand();

                    //var data2 = client.Updateable<agingrecord>()
                    //.SetColumns(it => new agingrecord() { 测试总结果 = "失败" })
                    //.Where(it => it.设备IP == "192.168.29.104"  && it.日期时间=="" &&it.序列号==""&&it.是否已经上传=="否").ExecuteCommand();

                    var data2 = client.Updateable<agingrecord>()
                    .SetColumns(it => new agingrecord() { 测试总结果 = "成功",生产地="天津",型号="2000E"})
                    .Where("ID = 134").ExecuteCommand();
                    //.Where(it => it.ID==134).ExecuteCommand();
                    //client.Aop.OnLogExecuting=(SQL,PA)
                }
            }
            catch(Exception ex) 
            { MessageBox.Show(ex.Message); }
        }
        public static void MySqlInsertTest()
        {
            try
            {
                using (SqlSugarClient client = new SqlSugarClient(_config))
                {
                    var data1 = new agingrecord() { 测试总结果="失败",作业员="测试" };
                    client.Insertable(data1).ExecuteCommand();
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }
        public static bool SQLite查询()
        {
            bool TF = false;
            //创建数据库连接：
            try
            {
                var connectionString = @"Data Source=" + PublicClass.Apppath + "datatable.db";
                var connection = new SQLiteConnection(connectionString);
                connection.Open();
                string SheetName = PublicClass.CosBobject.物料编码;
                var selectSql = "SELECT * FROM lhspec Where itemcode='" + SheetName + "'";
                var selectCommand = new SQLiteCommand(selectSql, connection);

                using (var reader = selectCommand.ExecuteReader())
                {
                    if (reader.HasRows) 
                    {
                        while (reader.Read())
                        {
                            PublicClass.CosBobject.产品型号     = reader.GetValue(1).ToString();
                            PublicClass.CosBobject.单元报警温度 = reader.GetValue(3).ToString();
                            PublicClass.CosBobject.水冷报警温度 = reader.GetValue(2).ToString();
                            PublicClass.CosBobject.一阶段时间 = reader.GetValue(4).ToString();
                            PublicClass.CosBobject.二阶段时间 = reader.GetValue(5).ToString();
                            PublicClass.CosBobject.三阶段时间 = reader.GetValue(6).ToString();
                            PublicClass.CosBobject.一阶段电流 = reader.GetValue(7).ToString();
                            PublicClass.CosBobject.二阶段电流 = reader.GetValue(8).ToString();
                            PublicClass.CosBobject.三阶段电流 = reader.GetValue(9).ToString();
                        }
                    }     
                    else
                    {
                        PublicClass.CosBobject.产品型号 = "数据库中无此型号！";
                        PublicClass.CosBobject.单元报警温度 = "0";
                        PublicClass.CosBobject.水冷报警温度 = "0";
                        PublicClass.CosBobject.一阶段时间 = "0";
                        PublicClass.CosBobject.二阶段时间 = "0";
                        PublicClass.CosBobject.三阶段时间 = "0";
                        PublicClass.CosBobject.一阶段电流 = "0";
                        PublicClass.CosBobject.二阶段电流 = "0";
                        PublicClass.CosBobject.三阶段电流 = "0";
                    }
                }
                connection.Close();
                TF = true;
                return TF;
            }
            catch(Exception ex)
            {
                PublicClass.NLOGex(ex);
                return false;
            }
        }
        public static void SQLite更新()
        {
            //创建数据库连接：
            var connectionString = @"Data Source=D:\SQLite\sample.db";
            var connection = new SQLiteConnection(connectionString);
            connection.Open();

            var query = "SELECT * FROM students ";
            DataTable dt = new DataTable();
            SQLiteCommand cmd = new SQLiteCommand(query, connection);
            SQLiteDataReader SQLitereader = cmd.ExecuteReader();
            dt.Load(SQLitereader);
            DataView dv = dt.DefaultView;


            //准备 SQL 命令：
            var sql = "CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INT)";
            //执行 SQL 命令：
            var command = new SQLiteCommand(sql, connection);
            command.ExecuteNonQuery();
            //插入数据：
            //var insertSql = "INSERT INTO students (name, age) VALUES (@Name, @Age)";
            //var insertCommand = new SQLiteCommand(insertSql, connection);
            //insertCommand.Parameters.AddWithValue("@Name", "李四");
            //insertCommand.Parameters.AddWithValue("@Age", "20");
            //insertCommand.ExecuteNonQuery();
            //查询数据：
            var selectSql = "SELECT * FROM students ";
            var selectCommand = new SQLiteCommand(selectSql, connection);

            using (var reader = selectCommand.ExecuteReader())
            {


                while (reader.Read())
                {
                    //数据库数据.ItemsSource = dv;  //设置DataGrid的ItemsSource属性
                    //var result = Encoding.GetEncoding("UTF-8").GetString(byteresult);
                    //Console.WriteLine($"ID: {reader.GetInt32(0)}, 姓名：{reader.GetString(1)}, 年龄：{reader.GetInt32(2)}");
                }
            }

            connection.Close();


            //DataSet ds = new DataSet();
            //SqlDataAdapter da = new SqlDataAdapter(sql, connection);  //创建SqlDataAdapter实例da，并指定SQL查询string和SqlConnection

            //da.Fill(ds, "Staff");  //从数据库中读取数据，并填充ds
            //DataView dv = new DataView(ds.Tables["Staff"]); //创建DataView实例dv，并指定其DataTable

        }
        public static void SQLite插入()
        {
            //创建数据库连接：
            var connectionString = @"Data Source=D:\SQLite\datatable.db";
            var connection = new SQLiteConnection(connectionString);
            connection.Open();


            //创建表：
            var sql = "CREATE TABLE IF NOT EXISTS agingtable (id INTEGER PRIMARY KEY AUTOINCREMENT, batchnumber TEXT, model TEXT, agingcurrent TEXT, agingtime TEXT, agingunit TEXT, endingtime TEXT)";
            var command = new SQLiteCommand(sql, connection);
            command.ExecuteNonQuery();
            //插入数据：
            //var insertSql = "INSERT INTO " + SheetName + " (batchnumber, model, agingcurrent, agingtime, agingunit, endingtime) VALUES (@batchnumber, @model, @agingcurrent, @agingtime, @agingunit, @endingtime)";
            //var insertCommand = new SQLiteCommand(insertSql, connection);
            //insertCommand.Parameters.AddWithValue("@batchnumber", Convert.ToString(row["batchnumber"]));
            //insertCommand.Parameters.AddWithValue("@model", Convert.ToString(row["model"]));
            //insertCommand.Parameters.AddWithValue("@agingcurrent", Convert.ToDouble(row["agingcurrent"]));
            //insertCommand.Parameters.AddWithValue("@agingtime", Convert.ToDouble(row["agingtime"]));
            //insertCommand.Parameters.AddWithValue("@agingunit", Convert.ToDouble(row["agingunit"]));
            //insertCommand.Parameters.AddWithValue("@endingtime", Convert.ToDouble(row["endingtime"]));
            //insertCommand.ExecuteNonQuery();

            connection.Close();

            //DataSet ds = new DataSet();
            //SqlDataAdapter da = new SqlDataAdapter(sql, connection);  //创建SqlDataAdapter实例da，并指定SQL查询string和SqlConnection

            //da.Fill(ds, "Staff");  //从数据库中读取数据，并填充ds
            //DataView dv = new DataView(ds.Tables["Staff"]); //创建DataView实例dv，并指定其DataTable
        }
        public static bool SQLitelhspec()
        {
            bool rs = false;
            string ConnectionString = "SERVER=192.168.5.80;UID=test;PWD=test;DATABASE=test;PORT=3306;CHARSET=gbk";
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    //连接数据库
                    conn.Open();
                    string SheetName = "lhspec";
                    string commandStr = "  select  *   from  " + SheetName + "  ";
                    MySqlCommand MySqlCmd = new MySqlCommand(commandStr, conn);
                    MySqlDataAdapter MySqlDataAda = new MySqlDataAdapter(MySqlCmd);

                    DataSet daSet = new DataSet();
                    DataTable myDataTable = new DataTable();
                    DataRow[] myDataRow;
                    MySqlDataAda.Fill(myDataTable);
                    myDataRow = myDataTable.Select();                    
                    MySqlCmd.ExecuteNonQuery();

                    //创建数据库连接：
                    var connectionString = @"Data Source=" + PublicClass.Apppath + "datatable.db";
                    var connection = new SQLiteConnection(connectionString);
                    connection.Open();

                    //清空原有
                    var query = "DELETE FROM " + SheetName + " ";
                    DataTable dt = new DataTable();
                    SQLiteCommand cmd = new SQLiteCommand(query, connection);
                    cmd.ExecuteNonQuery();

                    foreach (DataRow row in myDataTable.Rows)
                    {

                        string itemcode = Convert.ToString(row["itemcode"]);
                        string MODEL = Convert.ToString(row["MODEL"]);
                        double temp = Convert.ToDouble(row["temp"]);
                        double alarmtemp = Convert.ToDouble(row["alarmtemp"]);

                        double time1 = Convert.ToDouble(row["time1"]);
                        double time2 = Convert.ToDouble(row["time2"]);
                        double time3 = Convert.ToDouble(row["time3"]);
                        double current1 = Convert.ToDouble(row["current1"]);
                        double current2 = Convert.ToDouble(row["current2"]);
                        double current3 = Convert.ToDouble(row["current3"]);
                        //var insertSql = "INSERT INTO " + SheetName + " (itemcode,MODEL,temp,alarmtemp,time1,time2,time3,current1,current2,current3) VALUES (@itemcode,@MODEL,@temp,@alarmtemp,@time1,@time2,@time3,@current1,@current2,@current3)";
                        var insertSql = "INSERT INTO " + SheetName + " (itemcode,MODEL,temp,alarmtemp,time1,time2,time3,current1,current2,current3) VALUES ('" + itemcode + "','" + MODEL + "'," + temp + "," + alarmtemp + "," + time1 + "," + time2 + "," + time3 + "," + current1 + "," + current2 + "," + current3 + ")";
                        var insertCommand = new SQLiteCommand(insertSql, connection);
                        insertCommand.ExecuteNonQuery();                      
                    }             
                    connection.Close();
                    rs = true;
                    return rs;
                }
                catch (Exception ex)
                {
                    return rs;
                    //MessageBox.Show(ex.Message);
                }
            }

        }



    }
}
